
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 05/17/2012 13:53:32
-- Generated from EDMX file: D:\SUPINFO\Projets\Web\FlyingClub\FlyingClub\Models\DBModels.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [FlyingClubDB];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_MemberFlight]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Flight] DROP CONSTRAINT [FK_MemberFlight];
GO
IF OBJECT_ID(N'[dbo].[FK_MemberLesson]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Flight] DROP CONSTRAINT [FK_MemberLesson];
GO
IF OBJECT_ID(N'[dbo].[FK_PlaneFlight]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Flight] DROP CONSTRAINT [FK_PlaneFlight];
GO
IF OBJECT_ID(N'[dbo].[FK_FlightTransaction]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Flight] DROP CONSTRAINT [FK_FlightTransaction];
GO
IF OBJECT_ID(N'[dbo].[FK_MemberTransaction1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Transaction] DROP CONSTRAINT [FK_MemberTransaction1];
GO
IF OBJECT_ID(N'[dbo].[FK_MemberTransaction]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Transaction] DROP CONSTRAINT [FK_MemberTransaction];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Member]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Member];
GO
IF OBJECT_ID(N'[dbo].[Plane]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Plane];
GO
IF OBJECT_ID(N'[dbo].[Flight]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Flight];
GO
IF OBJECT_ID(N'[dbo].[Transaction]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Transaction];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Member'
CREATE TABLE [dbo].[Member] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Email] nvarchar(max)  NOT NULL,
    [Password] nvarchar(max)  NOT NULL,
    [Firstname] nvarchar(max)  NOT NULL,
    [Lastname] nvarchar(max)  NOT NULL,
    [InstructorNumber] int  NOT NULL,
    [Wallet] int  NOT NULL,
    [AdhesionDate] datetime  NOT NULL,
    [AdhesionFees] datetime  NOT NULL,
    [Staff] bit  NOT NULL,
    [AddressNumber] nvarchar(max)  NULL,
    [AddressStreet] nvarchar(max)  NULL,
    [AddressCode] nvarchar(max)  NULL,
    [AddressCity] nvarchar(max)  NULL,
    [AddressCountry] nvarchar(max)  NULL,
    [Gender] bit  NOT NULL
);
GO

-- Creating table 'Plane'
CREATE TABLE [dbo].[Plane] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Number] nvarchar(max)  NOT NULL,
    [HourRate] int  NOT NULL
);
GO

-- Creating table 'Flight'
CREATE TABLE [dbo].[Flight] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [DepartureDate] datetime  NOT NULL,
    [DepartureAirfield] nvarchar(max)  NOT NULL,
    [ArrivalDate] datetime  NOT NULL,
    [ArrivalAirfield] nvarchar(max)  NOT NULL,
    [PilotID] int  NOT NULL,
    [StudentID] int  NULL,
    [PlaneID] int  NOT NULL,
    [TransactionID] int  NOT NULL
);
GO

-- Creating table 'Transaction'
CREATE TABLE [dbo].[Transaction] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Payload] int  NOT NULL,
    [Description] nvarchar(max)  NULL,
    [Date] datetime  NOT NULL,
    [CreditID] int  NOT NULL,
    [DebtorID] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [ID] in table 'Member'
ALTER TABLE [dbo].[Member]
ADD CONSTRAINT [PK_Member]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Plane'
ALTER TABLE [dbo].[Plane]
ADD CONSTRAINT [PK_Plane]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Flight'
ALTER TABLE [dbo].[Flight]
ADD CONSTRAINT [PK_Flight]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Transaction'
ALTER TABLE [dbo].[Transaction]
ADD CONSTRAINT [PK_Transaction]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [PilotID] in table 'Flight'
ALTER TABLE [dbo].[Flight]
ADD CONSTRAINT [FK_MemberFlight]
    FOREIGN KEY ([PilotID])
    REFERENCES [dbo].[Member]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_MemberFlight'
CREATE INDEX [IX_FK_MemberFlight]
ON [dbo].[Flight]
    ([PilotID]);
GO

-- Creating foreign key on [StudentID] in table 'Flight'
ALTER TABLE [dbo].[Flight]
ADD CONSTRAINT [FK_MemberLesson]
    FOREIGN KEY ([StudentID])
    REFERENCES [dbo].[Member]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_MemberLesson'
CREATE INDEX [IX_FK_MemberLesson]
ON [dbo].[Flight]
    ([StudentID]);
GO

-- Creating foreign key on [PlaneID] in table 'Flight'
ALTER TABLE [dbo].[Flight]
ADD CONSTRAINT [FK_PlaneFlight]
    FOREIGN KEY ([PlaneID])
    REFERENCES [dbo].[Plane]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PlaneFlight'
CREATE INDEX [IX_FK_PlaneFlight]
ON [dbo].[Flight]
    ([PlaneID]);
GO

-- Creating foreign key on [TransactionID] in table 'Flight'
ALTER TABLE [dbo].[Flight]
ADD CONSTRAINT [FK_FlightTransaction]
    FOREIGN KEY ([TransactionID])
    REFERENCES [dbo].[Transaction]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_FlightTransaction'
CREATE INDEX [IX_FK_FlightTransaction]
ON [dbo].[Flight]
    ([TransactionID]);
GO

-- Creating foreign key on [CreditID] in table 'Transaction'
ALTER TABLE [dbo].[Transaction]
ADD CONSTRAINT [FK_MemberTransaction1]
    FOREIGN KEY ([CreditID])
    REFERENCES [dbo].[Member]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_MemberTransaction1'
CREATE INDEX [IX_FK_MemberTransaction1]
ON [dbo].[Transaction]
    ([CreditID]);
GO

-- Creating foreign key on [DebtorID] in table 'Transaction'
ALTER TABLE [dbo].[Transaction]
ADD CONSTRAINT [FK_MemberTransaction]
    FOREIGN KEY ([DebtorID])
    REFERENCES [dbo].[Member]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_MemberTransaction'
CREATE INDEX [IX_FK_MemberTransaction]
ON [dbo].[Transaction]
    ([DebtorID]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------